﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Text;

using Obsidian.Data.Sql;

namespace Obsidian.Data.Sql.SqlSrvClient
{
    /// <summary>
    /// 提供执行数据访问
    /// author:郑金泉
    /// </summary>
    public sealed class SqlSrvExecutor
    {

        #region #

        private static Hashtable parametersCache = Hashtable.Synchronized(new Hashtable());

        private string _connectionString = null;

        public SqlSrvExecutor() { }

        public SqlSrvExecutor(string connectionString)
        {
            this._connectionString = connectionString;
        }

        /// <summary>
        /// 设置Command对象
        /// </summary>
        /// <param name="commandType">执行类型</param>
        /// <param name="commandText">SQL文本</param>
        /// <param name="transaction">事务</param>
        private void PrepareCommand(SqlCommand command, CommandType commandType, string commandText, SqlParameter[] sqlParams, SqlTransaction transaction)
        {
            command.CommandType = commandType;

            if (String.IsNullOrEmpty(commandText))
                throw new ArgumentNullException("commandText");
            else
                command.CommandText = commandText;

            if (sqlParams != null)
            {
                foreach (SqlParameter p in sqlParams)
                {
                    command.Parameters.Add(p);
                }
            }

            if (transaction != null)
                command.Transaction = transaction;

            SqlConnection connection = command.Connection;
            connection.ConnectionString = this._connectionString;
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }

        public string ConnectionString
        {
            get { return this._connectionString; }
            set { this._connectionString = value; }
        }

        #endregion

        #region 参数缓存

        /// <summary>
        /// 重新得到缓存中的参数
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static SqlParameter[] GetCacheParameters(string key)
        {
            SqlParameter[] cachedParams = (SqlParameter[])parametersCache[key];

            if (cachedParams == null)
                return null;

            SqlParameter[] clonedParams = new SqlParameter[cachedParams.Length];

            for (int i = 0, j = clonedParams.Length; i < j; i++)
                clonedParams[i] = (SqlParameter)((ICloneable)cachedParams[i]).Clone();

            return clonedParams;
        }

        /// <summary>
        /// 设置参数数组到缓存
        /// </summary>
        /// <param name="key"></param>
        /// <param name="sqlParams"></param>
        public static void SetCacheParameters(string key, SqlParameter[] sqlParams)
        {
            if (key == null || String.IsNullOrEmpty(key))
                throw new ArgumentNullException("key");
            if (sqlParams == null)
                throw new ArgumentNullException("sqlParams");

            parametersCache[key] = sqlParams;
        }

        #endregion

        #region 执行并返回受影响的行数



        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string commandText)
        {
            return SqlExecuteNonQuery(CommandType.Text, commandText, null);
        }


        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return SqlExecuteNonQuery(commandType, commandText, null);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string commandText, SqlParameter[] sqlParams)
        {
            return SqlExecuteNonQuery(CommandType.Text, commandText, sqlParams);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText, SqlParameter[] sqlParams)
        {
            return SqlExecuteNonQuery(commandType, commandText, sqlParams);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        private int SqlExecuteNonQuery(CommandType commandType, string commandText, SqlParameter[] sqlParams)
        {
            int n;
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, sqlParams, null);
                try { n = command.ExecuteNonQuery(); }
                catch { throw; }
                finally
                {
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return n;
        }

        #endregion

        #region 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行



        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(string commandText)
        {
            return SqlExecuteScalar(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            return SqlExecuteScalar(commandType, commandText, null);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(string commandText, SqlParameter[] sqlParams)
        {
            return SqlExecuteScalar(CommandType.Text, commandText, sqlParams);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(CommandType commandType, string commandText, SqlParameter[] sqlParams)
        {
            return SqlExecuteScalar(commandType, commandText, sqlParams);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        private object SqlExecuteScalar(CommandType commandType, string commandText, SqlParameter[] sqlParams)
        {
            object obj;
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, sqlParams, null);
                try { obj = command.ExecuteScalar(); }
                catch { throw; }
                finally
                {
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return obj;
        }

        #endregion

        #region 执行并返回DataSet

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText)
        {
            return SqlExecuteDataSet(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            return SqlExecuteDataSet(commandType, commandText, null);
        }


        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText, SqlParameter[] sqlParams)
        {
            return SqlExecuteDataSet(CommandType.Text, commandText, sqlParams);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText, SqlParameter[] sqlParams)
        {
            return SqlExecuteDataSet(commandType, commandText, sqlParams);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        private DataSet SqlExecuteDataSet(CommandType commandType, string commandText, SqlParameter[] sqlParams)
        {
            DataSet dataSet = new DataSet();
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, sqlParams, null);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                try { dataAdapter.Fill(dataSet); }
                catch
                {
                    dataSet.Dispose();
                    throw;
                }
                finally
                {
                    dataAdapter.Dispose();
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return dataSet;
        }

        #endregion

        #region 执行并返回DataReader

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string commandText)
        {
            return SqlExecuteReader(CommandType.Text, commandText, null, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return SqlExecuteReader(commandType, commandText, null, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string commandText, SqlParameter[] sqlParams)
        {
            return SqlExecuteReader(CommandType.Text, commandText, sqlParams, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlParameter[] sqlParams)
        {
            return SqlExecuteReader(commandType, commandText, sqlParams, CommandBehavior.CloseConnection);
        }


        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string commandText, CommandBehavior behavior)
        {
            return SqlExecuteReader(CommandType.Text, commandText, null, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(CommandType commandType, string commandText, CommandBehavior behavior)
        {
            return SqlExecuteReader(commandType, commandText, null, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(string commandText, SqlParameter[] sqlParams, CommandBehavior behavior)
        {
            return SqlExecuteReader(CommandType.Text, commandText, sqlParams, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlParameter[] sqlParams, CommandBehavior behavior)
        {
            return SqlExecuteReader(commandType, commandText, sqlParams, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        private SqlDataReader SqlExecuteReader(CommandType commandType, string commandText, SqlParameter[] sqlParams, CommandBehavior behavior)
        {
            SqlDataReader dataReader;
            SqlConnection connection = new SqlConnection(this._connectionString);
            SqlCommand command = connection.CreateCommand();
            PrepareCommand(command, commandType, commandText, sqlParams, null);
            try { dataReader = command.ExecuteReader(behavior); }
            catch { throw; }
            finally
            {
                command.Parameters.Clear();
                command.Dispose();
            }
            return dataReader;
        }

        #endregion

        #region 执行分页查询返回DataReader
        /*
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="idField">主键字段名</param>
        /// <param name="selectFields">查询字段</param>
        /// <param name="whereText">查询条件</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="orderType">排序类型</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="count">总数据量</param>
        /// <returns></returns>
        public SqlDataReader PaginationReader(string tableName, string idField, string[] selectFields, string whereText, string orderField, SortBy sortBy, int pageIndex, int pageSize, out int count)
        {
            //分页存储过程名
            string PROC_NAME = "sp_pagination";
            //查询字段
            if (selectFields.Length == 0)
                throw new ArgumentNullException("查询字段不能为空!");
            StringBuilder fieldsBuilder = new StringBuilder();
            foreach (string f in selectFields)
                fieldsBuilder.Append("," + f.Trim());
            fieldsBuilder.Remove(0, 1);
            //排序类型
            int ot = sortBy == SortBy.Desc ? 1 : 0;

            //创建参数数组
            SqlParameter[] paramsArray = new SqlParameter[] { 
                new SqlParameter("@tableName", SqlDbType.VarChar, 50),
                new SqlParameter("@idField",SqlDbType.VarChar,50),
                new SqlParameter("@selectFields",SqlDbType.VarChar,1000),
                new SqlParameter("@whereStr",SqlDbType.VarChar,1000),
                new SqlParameter("@orderField",SqlDbType.VarChar,50),
                new SqlParameter("@orderType",SqlDbType.Bit),
                new SqlParameter("@pageIndex",SqlDbType.Int),
                new SqlParameter("@pageSize",SqlDbType.Int)
            };
            tableName =tableName.Trim() ;
            idField = idField.Trim();
            orderField = orderField.Trim();
            if (fieldsBuilder.ToString().IndexOf(orderField) == -1)
                fieldsBuilder.Append("," + orderField);

            //参数赋值
            //表名
            paramsArray[0].Value = tableName;
            //主键字段名
            paramsArray[1].Value = idField;
            //查询字段
            paramsArray[2].Value = fieldsBuilder.ToString();
            //where条件
            paramsArray[3].Value = whereText;
            //排序字段
            paramsArray[4].Value = orderField;
            //排序类型
            paramsArray[5].Value = ot;
            //查询页码
            paramsArray[6].Value = pageIndex;
            //每页数据量
            paramsArray[7].Value = pageSize;


            //---开始分页查询---
            SqlConnection connection = new SqlConnection(this._connectionString);
            SqlCommand command = connection.CreateCommand();
            SqlDataReader dataReader;
            //配置command
            PrepareCommand(command, CommandType.StoredProcedure, PROC_NAME, paramsArray, null);
            try { dataReader = command.ExecuteReader(CommandBehavior.CloseConnection); }
            catch
            {
                throw;
            }
            finally
            {
                command.Dispose();
            }
            count = 0;
            if (dataReader.Read())
                //读取总数据量
                count = dataReader.GetInt32(0);
            dataReader.NextResult();
            return dataReader;
        }
        */

        #region PaginationReader分页方法所需的存储过程

        /*=============================================================

        -- top in 分页存储过程
        create proc sp_pagination
        @tableName varchar(50),	--表名
        @idField varchar(50), -- 主键字段
        @selectFields varchar(1000), --查询字段
        @whereStr varchar(1000), --where查询条件
        @orderField varchar(50), --排序字段
        @orderType bit, --排序类型 0 is "ASC" 1 is "DESC"
        @pageIndex int, --页码
        @pageSize int --每页显示数据量
        as
        declare @orderA varchar(5)
        declare @orderB varchar(5)
        declare @sqlText nvarchar(2000)

        if(@orderType = 1) begin
            set @orderA = 'DESC'
            set @orderB = 'ASC'
        end
        else
        begin
            set @orderA = 'ASC'
            set @orderB = 'DESC'
        end

        if(@whereStr != '')
        begin
            set @whereStr = ' where ' + @whereStr
        end
	
        declare @count int
            set @sqlText = ' select @a = count(*) from ' + @tableName + @whereStr
            exec sp_executesql @sqlText,N'@a int output',@count output
        select @count

        declare @intTop int
        set @intTop = @pageSize * @pageIndex

	    if(@intTop > @count)
	    begin
	        set @intTop = @count
	        set @pageSize = @count % @pageSize
	    end

        if(@pageIndex = 1)
        begin
            set @sqlText = ' select top ' + str(@intTop) + ' ' + @selectFields + '  from ' + @tableName  + @whereStr + ' order by ' + @orderField + ' ' + @orderA
        end 
        else
        begin
        set @sqlText =	' select * from ' +
                            ' (select top ' + str(@pageSize) + ' ' + @selectFields + ' from ' + @tableName + ' where ' + @idField + ' in ' +
					            ' (select top ' + str(@intTop) + ' ' + @idField + ' from ' + @tableName + ' ' +  @whereStr + ' order by ' + @orderField + ' ' + @orderA + ')' +
				            ' order by ' + @orderField + ' ' + @orderB + ') as tmp' +
			            ' order by tmp.' + @orderField + ' '  + @orderA
        end
        exec(@sqlText)


    =============================================================*/

        #endregion


        #endregion

        #region 读取表结构
        /*

        public DbTable GetTableSchema(string name)
        {
            using (SqlConnection connection = new SqlConnection(this._connectionString))
            {
                connection.Open();
                DataTable table = connection.GetSchema("columns", new string[] { null, null, name, null });
                if (table.Rows.Count == 0)
                    throw new Exception("找不到映射表 - " + name);
                List<DbField> fieldList = new List<DbField>();
                DbTable dbtable = new DbTable(name);
                foreach (DataRow r in table.Rows)
                {
                    string columnName = r[3].ToString();//列名
                    bool isPK = r[4].ToString() == "1";//主键,表的第一位要是主键
                    object defaultValue = r[5];//默认值
                    bool allowNull = r[6].ToString() == "YES";//是否允许为空
                    string type = r[7].ToString();//数据类型
                    int size = Convert.IsDBNull(r[8]) ? -1 : Convert.ToInt32(r[8]);//数据大小
                    bool isUNI = r[4].ToString() == "1";//是否是唯一约束
                    bool autoIncrement = r[4].ToString() == "1";//主键

                    DbField field = new DbField(
                        dbtable,
                        columnName,
                        isPK,
                        allowNull,
                        autoIncrement,
                        isUNI,
                        defaultValue,
                        ToDataType(type),
                        size
                        );
                    fieldList.Add(field);
                }
                dbtable.SetFields(fieldList.ToArray());
                connection.Close();
                return dbtable;
            }
        }

        private DataType ToDataType(string type)
        {
            switch (type.ToLower())
            {
                case "bit":
                    return DataType.Bit;
                case "int":
                    return DataType.Int;
                case "bigint":
                    return DataType.Bigint;
                case "char":
                    return DataType.Char;
                case "varchar":
                case "nvarchar":
                    return DataType.VarChar;
                case "text":
                case "ntext":
                    return DataType.Text;
                case "date":
                case "datetime":
                    return DataType.DateTime;
                case "decimal":
                    return DataType.Decimal;
                default:
                    throw new Exception("找不到对应类型 - " + type);
            }
        }
         * */
        #endregion
    
    }
}
